PHP 5 sessions in mysql database with PDO db objects

chris (2007-06-11 20:25:58)
17944 views
5 replies
In PHP 5.0.5 and later, all objects are destroyed before the session 'write' and 'close' functions are called. This means you can't use any objects in your 'write' or 'close' session handlers with the vanilla PHP insallation.

This was met with some very luke-warm commentary in bugzilla and various blogs and forums around the community. Sample bugs filed include Bug #27555 and Bug #33642. The documentation was slow to update, but now reads as follows:

"Write and Close handlers are called after destructing objects since PHP 5.0.5. Thus destructors can use sessions but session handler can't use objects. In prior versions, they were called in the opposite order. It is possible to call session_write_close() from the destructor to solve this chicken and egg problem."

So what they are telling us is that when a PHP script ends, the process which clears up any objects, destroying them and de-allocating memory consumed by them has been jiggled about and now runs just before the session 'write' and 'close' handlers are called. This means that if your write and close functions use a database connection object (such as that created with ADO or PDO or Pear/DB), these connection objects will have been destroyed at this point and your sessions will therefore never update.

In fact, you will most probably get an error when your 'write' function runs, which will look like "Fatal error: Cannot access empty property in <path to script here> one line <line number here>". This means that PHP can no longer find the property at that line - probably the database connection object, which has been destroyed and thrown out of the window by PHP.

As the documentation suggests, fixing this requires using the session_write_close() function in your destructor. But what destructor are they talking about? How does this relate to what is normally just a list of callback functions?

Well, remember that the way we assign the session callback routines is via session_set_save_handler(), the function does also allow you to use methods (from within an object) as your callbacks. So imagine you have created a class to contain all your session functions. Let's call this class 'Session'. You could then create an object of class 'Session' into a variable $session and then use the internal methods as callbacks like this:
        $session = new Session;

        session_set_save_handler(
                array(&$session, "open"),
                array(&$session, "close"),
                array(&$session, "read"),
                array(&$session, "write"),
                array(&$session, "destroy"),
                array(&$session, "gc")
        );

This gives us a chance to hook into PHP's internal session handling magic by overriding the __destruct() method of this class and making it call the session_write_close() function. This just means that when the PHP core tries to destroy the $session object, the __destruct() method (which gets called when any object goes through destruction) will have an opportunity to call the 'write' and 'close' methods of the class. We can leave these to behave as intended and the functionality will remain unchanged.

As for the database connection object, you can implement a further bit of niftiness to re-use the connection handler for any further database work required during the lifetime of your script. This you can do simply by declaring a $db variable and making it public, so that other parts of the script can make use of it.

The rest of this article demonstrates a working example of this sessions implementation. If you want to learn from example, read on!

This example specifically uses PDO to provide the database connectivity and query preparation. You might be using ADO, or Pear/DB, or maybe you are using php's native database functions to drive your session. If so, you will have to adapt the code to suite your preferred techniques. If you are interested in PDO and getting that to work with PHP, click back through the breadcrumb at the top of this page, since there are some articles elsewhere on the channels which help to illustrate the PDO extension.


The code below demonstrates all of the above principles and shows at the end that we are able to write session data to the database as well as read from it - and then we can re-use the database object in the rest of the script. The only things you will have to change to make this work are:

1) Ensure that the initial include file contains appropriate database settings for your db.
2) Ensure that the variable names in the db settings file match those in the 'open' method.


So first, assuming you have a database already up and running and you are able to connect to it from your PHP script, here is how you might add a table to handle your sessions:
CREATE TABLE `sessions` (
  `se_id` varchar(50) NOT NULL default '',
  `se_value` text,
  `se_expires` int(11) default NULL,
  PRIMARY KEY  (`se_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

At this point, you can copy the code I have used to provide the sessions functionality:

<?php
        ob_start();
        include '/var/www/includes/config.inc.php';

        class Session {
                public $db;
                // public $maxlifetime = get_cfg_var("session.gc_maxlifetime");
                public $maxlifetime = 1800; /* 30 mins */
                public $expiry;

                public function __destruct(){
                        session_write_close();
                }

                public function open( $path, $name ) {
                        global $db_type, $db_name, $db_hostname, $db_username, $db_password;
                        $this->db = new PDO("$db_type:dbname=$db_name;host=$db_hostname", "$db_username", "$db_password");
                        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                        return true;
                }

                public function close() {
                        return true;
                }

                public function read($se_id){
                        $qry = "select se_value from sessions where se_id = '$se_id' and se_expires > " . time();
                        $sth = $this->db->prepare($qry);
                        $sth->execute();
                        $result = $sth->fetch(PDO::FETCH_ASSOC);
                        return $result['se_value'];
                }

                public function write($se_id, $se_val){
                        $this->expiry = time() + $this->maxlifetime;
                        try {
                                $qry= "insert into sessions (se_id, se_value, se_expires) values('$se_id', '$se_val', $this->expiry)";
                                $sth = $this->db->prepare($qry);
                                $sth->execute();
                        } catch (PDOException $e) {
                                $qry= "update sessions set se_value='$se_val', se_expires=$this->expiry where se_id='$se_id'";
                                $sth = $this->db->prepare($qry);

                                $sth->execute();
                        }
                }

                public function destroy($se_id){
                        $qry = "delete from sessions where se_id ='$se_id'";
                        $sth = $this->db->prepare($qry);
                        $tot= $sth->execute();
                        return ($tot);
                }

                public function gc($maxlifetime){
                        $qry = "delete from sessions where se_expires < ".time();
                        $sth = $this->db->prepare($qry);
                        $tot= $sth->execute();
                        return ($tot);
                }
        }

        $session = new Session;
        session_set_save_handler(
                array(&$session, "open"),
                array(&$session, "close"),
                array(&$session, "read"),
                array(&$session, "write"),
                array(&$session, "destroy"),
                array(&$session, "gc")
        );

        session_start();

	// show that we can write to the session

        $_SESSION['hello']='brave new world';
        print_r($_SESSION);

        // and demonstrate that I can use this $db handler to talk to the db

        $db =& $session->db;
        $sth = $db->prepare("select now() as t");
        $sth->execute();
        $row = $sth->fetch();

        echo "<br><br>did a query for the current time and got back ".$row['t'];
	
?>


That's really it! You how have a database-backed, object-oriented session handler for PHP5, which you can roll into any application from now on. However, before I log off, I'll just point out a couple of additional points:

Firstly, note that you can define the maximum life of your session in either of two ways:

// public $maxlifetime = get_cfg_var("session.gc_maxlifetime");
public $maxlifetime = 1800; /* 30 mins */

The first technique (which I have commented out in this example) loads the session.gc_maxlifetime from the php.ini file. This means that you can control session lifetimes for all php applications running under a single Apache installation. The alternative to this (shown in the uncommented line) shows how you can simply override this by defining your own timeout in seconds and then summing that value with the current unix timestamp to create an expiry time in the future.

I can't remember what the second point was I was going to make - so I'll just go and have a beer instead.

I hope that's useful - please comment on whether or not this works for you!

christo
comment
Martin Hermosilla
2009-04-14 17:59:48

My Implementation is not working!

I'm facing some issues here, I was implementing something simmilar to this but instead of calling session_set_save_handler after new Session() assignment I have included a Constructor in the class which calls sesion_set_save_handler like this, also I'm using PEAR::DB


function Session()
{
/* some vars to be initialized */
session_set_save_handler(
array(&$session, "open"),
array(&$session, "close"),
array(&$session, "read"),
array(&$session, "write"),
array(&$session, "destroy"),
array(&$session, "gc")
);
}

the thing is, session is created with no problems, but the writting/reading seems not to be workin :(

this is my class:
<?php
ob_start();

require_once 'config.php';
require_once 'db.php';
require_once 'session_cfg.php';

global $session_table;
global $lifetime;

class Session
{

    var $session_table;
    public $session_db;
    public $lifetime;
    public $expiration;

    function Session()
    {
        global $max_lifetime;
        global $sess_table;
        $this->session_table = $sess_table;
        $this->lifetime      = $max_lifetime;
        $this->setSessionHandler();
    }

    public function __destruct()
    {
        session_write_close();
    }

    public function setSessionHandler()
    {
           session_set_save_handler(array(&$this, "session_open")   ,
                                    array(&$this, "session_close")  ,
                                    array(&$this, "session_read")   ,
                                    array(&$this, "session_write")  ,
                                    array(&$this, "session_destroy"),
                                    array(&$this, "session_garbage_collect")
                                   );
    }

    public function session_open($session_save_path, $session_name)
    {
        $this->session_db = getDataSource();
        return true;
    }

    public function session_close()
    {
        return true;
    }

    public function session_read($SID)
    {
        $query  = "SELECT value FROM $this->session_table WHERE session_id = '$SID' and session_expiration >". time();
        $result = $this->session_db->query( $query );
    }

    public function session_write($SID, $value)
    {
        $this->expiration = time() + $this->lifetime;
        $query      = "INSERT INTO $this->session_table VALUES('$SID', '$this->expiration', '$value')";
        $result     = $this->session_db->query( $query );
        if ( !$result ) :
           $query   = "UPDATE $this->session_table
                             SET session_expiration = '$this->expiration',
                                 session_value      = '$value',
                                 session_id         = '$SID'
                             AND session_expiration > " . time();
           $result  = $this->session_db->query( $query );
        endif;
    }

    public function session_destroy($sessionID)
    {
        $query  = "DELETE FROM $this->seesion_table WHERE session_id = '$sessionID'";
        $result = $this->session_db->query( $query );
    }

    public function session_garbage_collect($lifetime)
    {
        $query  = "DELETE FROM $this->session_table WHERE session_expiration < " . time() - $lifetime;
        $result = $this->session_db->query( $query );
        return $this->session_db->affectedRows();
    }
}
?>

Thanks in advance for your help.

Martin Hermosilla
http://www.martinh.cl/
http://www.programaticon.tk/
reply iconedit reply
martinh
2009-04-16 03:03:25

some mistakes:

where says global $session_table; must say: global $sess_table;
where says var $session_table; must say public $session_table;

Regards,

-Martin
http://www.martinh.cl/
http://www.programaticon.tk/


reply icon
Coyote6
2009-05-22 23:56:15

Session Class Help

Hi I was wondering if you might be able to help me out. I am trying to create a session class that calls my stored procedures inside the database. At first it seems to work. I login on the login page and it posts the session data as expected. But then I reload that page or go to a new one and the data stored in the database for that session is erased. It overwrites the data values. What is going on here? Any help is appreciated thanks.
reply iconedit reply
Coyote6
2009-06-02 08:06:40

My Code

Hi I was wondering if you might be able to help me out. I am trying to create a session class that calls my stored procedures inside the database. At first it seems to work. I login on the login page and it posts the session data as expected. But then I reload that page or go to a new one and the data stored in the database for that session is erased. It overwrites the data values. What is going on here? Any help is appreciated thanks.


Sorry it took a couple of days to get my code posted but I was testing everything I could think of. Like I said before it will write to the database but then when a new page is loaded I lose all of my session data. I read somewhere about the __sleep and __wakeup function possibly solving the issue but I have no idea how I would implement that into this if that is the case. Also I tried the insert and update method but I commented that out and tried using replace since my old non-pdo version used it. But that ended with the same results. Like I said any help would be greatly appreciated.

DB is my PDO class for the database.

My DB uses the table sess with fields sess_id, sess_access, sess_data.

Here are my stored procedures.

-- Select the session data.
CREATE PROCEDURE `get` (IN in_sess_id VARCHAR(32), IN in_time INT(10))
BEGIN
SELECT sess_data FROM sess WHERE sess_id=in_sess_id and sess_access>in_time;
END$$

-- Write the session data.
CREATE PROCEDURE `write` (IN in_sess_id VARCHAR(32), IN in_time INT(10), IN in_data TEXT)
BEGIN
REPLACE INTO sess VALUES (in_sess_id, in_time, in_data);
END$$

-- Write the session data.
CREATE PROCEDURE `insert` (IN in_sess_id VARCHAR(32), IN in_time INT(10), IN in_data TEXT)
BEGIN
INSERT INTO sess (sess_id, sess_access, sess_data) VALUES (in_sess_id, in_time, in_data);
END$$

-- Update the session data.
CREATE PROCEDURE `update` (IN in_sess_id VARCHAR(32), IN in_time INT(10), IN in_data TEXT)
BEGIN
UPDATE sess SET sess_access=in_time, sess_data=in_data WHERE sess_id=in_sess_id;
END$$

-- Delete the session data.
CREATE PROCEDURE `destroy` (IN in_sess_id VARCHAR(32))
BEGIN
DELETE FROM sess WHERE sess_id=in_sess_id;
END$$

-- Clean out old session data.
CREATE PROCEDURE `clean` (IN in_time INT(10))
BEGIN
DELETE FROM sess WHERE sess_access<in_time;
END$$


<?php

class Session {

// Variables.
private $max_session = 86400; // One day.

// Close session.
public function __destruct() {
session_write_close();
}

// Check to see if a connection is made to the db, start one if not.
public function open () {
return true;
}

// Close the dbc for the session.
public function close () {
return TRUE;
}

// Pull the data from the db to read.
public function read ($id) {
global $max_session;
$old = DB::escape_data(time() - $max_session);
$q = "CALL get (:id, :old)";
$p = array ();
$p[':id'] = $id;
$p[':old'] = $old;
return DBC::get_row($q, $p);
}

// Write the session info into the db.
public function write ($id, $data) {
$time = DB::escape_data (time());
$id = DB::escape_data ($id);
$data = DB::escape_data ($data);
$q = "CALL write (:id, :time, :data)";
$p = array ();
$p[':id'] = $id;
$p[':time'] = $time;
$p[':data'] = $data;
return DB::execute($q, $p);
/*try {
$q = "CALL insert (:id, :time, :data)";
$p = array ();
$p[':id'] = $id;
$p[':time'] = $time;
$p[':data'] = $data;
DB::execute($q, $p);
}
catch (PDOException $e) {
$q = "CALL update (:id, :time, :data)";
$p = array ();
$p[':id'] = $id;
$p[':time'] = $time;
$p[':data'] = $data;
DB::execute($q, $p);
}*/
}

// Destroy session data in db.
public function destroy ($id) {
$q = "CALL destroy (:id)";
$p = array ();
$p[':id'] = $id;
return DB::execute($q, $p);
}

// Clean out the old sessions.
public function clean ($max_session) {
global $max_session;
$old = DB::escape_data(time() - $max_session);
$q = "CALL clean (:old)";
$p = array ();
$p[':old'] = $old;
return DB::execute ($q, $p);
}

}
// Call the session handler and start the session.
$session = &new Session;
session_set_save_handler (array (&$session, "open"), array (&$session, "close"), array (&$session, "read"), array (&$session, "write"), array (&$session, "destroy"), array (&$session, "clean"));
session_start ();

?>

Thanks again.
reply iconedit reply
Coyote6
2009-06-02 21:42:44

Solved my problem.

Fixed...

For some reason I could not access my max_session variable so I hard coded that into the clean and read functions. Then I created a constructor to place the error handler inside of that but must leave the session_start() outside of the class... kinda goofy but I got it to work and I used the replace method for the write function too. Hopefully this may be helpful to someone in the future. Thanks.
reply iconedit reply